import pymysql
import pandas as pd
import sqlite3


def insert_data():
    day_data = pd.read_csv('../data/day_data.csv')
    month_data = pd.read_csv('../data/month_data.csv')
    day_data = day_data.values.tolist()
    month_data = month_data.values.tolist()
    # conn = pymysql.connect(host='database', port=3306, user='root', password='123456', db='tq')
    conn = sqlite3.connect('../../sqlite3.db')
    cursor = conn.cursor()

    sql = '''
    INSERT INTO day_data
    (id, city, hightest_tem, lowest_tem, weather, `date`, wind_direction, wind_level, weekday)
    VALUES(NULL, ?, ?, ?, ?, ?, ?, ?, ?);
    '''
    cursor.executemany(sql, day_data)
    sql = '''
    INSERT INTO month_data
    (id, city, `month`, avg_high_tem, avg_low_tem, extreme_high_tem, extreme_low_tem, avg_air_quality, best_air, best_air_date, worst_air, worst_air_date)
    VALUES(NULL, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);
    '''
    cursor.executemany(sql, month_data)
    conn.commit()
    cursor.close()
    conn.close()


if __name__ == '__main__':
    insert_data()